package org.wsdx.Util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class GetConnection {
	private static final String USER = "root";
	private static final String PASSW = "root";
	private static final String SQLURL = "jdbc:mysql://123.207.173.169:3306/wsdc?characterEncoding=utf8";
	static{
		try {
			Class.forName("com.mysql.jdbc.Connection");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public static Connection getConnection() throws SQLException{
		 Connection connection= DriverManager.getConnection(SQLURL, USER, PASSW);
		 connection.setAutoCommit(false);
		return connection;
	}
	public static Connection getConnection(boolean onlyread) throws SQLException{
		 Connection connection= DriverManager.getConnection(SQLURL, USER, PASSW);
		 connection.setReadOnly(onlyread);
		 connection.setAutoCommit(false);
		return connection;
	}
	
	
	static public List<? super Object> selectPage(int page,int offset) throws SQLException{
		List<? super Object> ls=new LinkedList<>();
		Connection connection=GetConnection.getConnection();
		PreparedStatement statement=connection.prepareStatement("select * from sg_heros order by id limit ?,?");
		statement.setInt(1,(page<0?0:page)*offset);
		statement.setInt(2, offset<0?0:offset);
		ResultSet resultSet=statement.executeQuery();
		while(resultSet.next()!=false){
			ls.add(resultSet.getString("name"));
		}
		connection.close();
		return ls;
	}
	static public List<? super Object> selectKeyPagination(String key) throws SQLException{
		List<? super Object> ls=new LinkedList<>();
		Connection connection=GetConnection.getConnection();
	PreparedStatement statement=connection.prepareStatement("select * from sg_heros  where name like ? or nickname like ? or birthyear like ? or country_id like ? order by id");
	//	PreparedStatement statement=connection.prepareStatement("select * from sg_heros order by sg_heros.name  where name like '曹操'");
		if(key==null||key.length()==0){
			key="%";
		}
		else{
			key="%"+key+"%";
		}
		statement.setString(1, key);
		statement.setString(2, key);
		statement.setString(3, key);
		statement.setString(4, key);
		ResultSet resultSet=statement.executeQuery();
		while(resultSet.next()!=false){
			StringBuilder sb=new StringBuilder();
			ls.add(sb.append(resultSet.getString("name")).append(resultSet.getString("nickname")).append(resultSet.getString("country_id")).append(resultSet.getString("birthyear")));
		}
		connection.close();
		return ls;
	}
	
	public static void main(String[] args) throws SQLException {
		System.out.println(selectKeyPagination(null));
	}
}
